package com.jiyici.data;

import java.util.ArrayList;

import jiyici.Constant.ModelColumn;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.jiyici.model.Category;
import com.jiyici.model.ClientBaseModel;
import com.jiyici.model.Item;
import com.jiyici.model.ItemParam;
import com.jiyici.model.ModelUtil;
import com.jiyici.model.Record;
import com.jiyici.model.RecordParam;
import com.jiyici.model.User;
import com.jiyici.utils.LogUtil;

public class ClientDBHelper extends SQLiteOpenHelper {
    
    private static final String TAG = "ClientDBHelper";
    
    private static final String CREATE_TABLE = "CREATE TABLE ";
    private static final String DROP_TABLE = "DROP TABLE IF EXISTS ";
//    private static final String ID_TYPE =  " INTEGER PRIMARY KEY";
    private static final String INTEGER_TYPE = " INTEGER";
    private static final String NUMERIC_TYPE = " NUMERIC";
    private static final String TEXT_TYPE = " TEXT";
    private static final String COMMA_SEP = ",";
    
    private static final String SEGMENT_CLIENT_BASE = ModelColumn.BASE_COLUMN_ID + INTEGER_TYPE + COMMA_SEP
            + ModelColumn.BASE_COLUMN_USERID + INTEGER_TYPE + COMMA_SEP
            + ModelColumn.BASE_COLUMN_LASTMODIFIED + INTEGER_TYPE + COMMA_SEP
            + ModelColumn.BASE_COLUMN_DELETED + NUMERIC_TYPE + COMMA_SEP
            + ModelColumn.BASE_COLUMN_DIRTY + NUMERIC_TYPE + COMMA_SEP;
    
    private static final String SQL_CREATE_CATEGORY = CREATE_TABLE + Category.class.getSimpleName()
            + " (" + SEGMENT_CLIENT_BASE
            + ModelColumn.CATEGORY_COLUMN_NAME + TEXT_TYPE + COMMA_SEP
            + ModelColumn.CATEGORY_COLUMN_DISPLAY_ORDER + TEXT_TYPE + ")";
    
    private static final String SQL_CREATE_ITEM = CREATE_TABLE + Item.class.getSimpleName()
            + " (" + SEGMENT_CLIENT_BASE
            + ModelColumn.ITEM_COLUMN_NAME + TEXT_TYPE + COMMA_SEP
            + ModelColumn.ITEM_COLUMN_CATEGORYID + INTEGER_TYPE + COMMA_SEP
            + ModelColumn.ITEM_COLUMN_DISPLAY_INDEX + INTEGER_TYPE + COMMA_SEP
            + ModelColumn.ITEM_COLUMN_RECORD_COUNT + INTEGER_TYPE + ")";
    
    private static final String SQL_CREATE_ITEM_PARAM = CREATE_TABLE + ItemParam.class.getSimpleName()
            + " (" + SEGMENT_CLIENT_BASE
            + ModelColumn.ITEM_PARAM_COLUMN_ITEMID + INTEGER_TYPE + COMMA_SEP
            + ModelColumn.ITEM_PARAM_COLUMN_NAME + TEXT_TYPE + COMMA_SEP
            + ModelColumn.ITEM_PARAM_COLUMN_TYPE + INTEGER_TYPE + COMMA_SEP
            + ModelColumn.ITEM_PARAM_COLUMN_IS_MUST + NUMERIC_TYPE  + COMMA_SEP 
            + ModelColumn.ITEM_PARAM_COLUMN_DISPLAY_ORDER + TEXT_TYPE + ")";
    
    private static final String SQL_CREATE_RECORD = CREATE_TABLE + Record.class.getSimpleName()
            + " (" + SEGMENT_CLIENT_BASE
            + ModelColumn.RECORD_ITEM_ID + INTEGER_TYPE + COMMA_SEP
            + ModelColumn.RECORD_RECORD_TIME + INTEGER_TYPE + ")";
    
    private static final String SQL_CREATE_RECORD_PARAM = CREATE_TABLE + RecordParam.class.getSimpleName()
            + " (" + SEGMENT_CLIENT_BASE
            + ModelColumn.RECORD_PARAM_RECORD_ID + INTEGER_TYPE + COMMA_SEP
            + ModelColumn.RECORD_PARAM_ITEM_PARAM_ID + INTEGER_TYPE + COMMA_SEP
            + ModelColumn.RECORD_PARAM_VALUE + TEXT_TYPE + ")";
    
    private static final String SQL_DELETE_CATEGORY = DROP_TABLE + Category.class.getSimpleName();

    private static final String SQL_DELETE_ITEM = DROP_TABLE + Item.class.getSimpleName();

    private static final String SQL_DELET_ITEM_PARAM = DROP_TABLE + ItemParam.class.getSimpleName();
    
    private static final String SQL_DELET_RECORD = DROP_TABLE + Record.class.getSimpleName();
    
    private static final String SQL_DELET_RECORD_PARAM = DROP_TABLE + RecordParam.class.getSimpleName();

    
    private static final int DATABASE_VERSION = 1;
    
    private static final String DATABASE_NAME = "jiyici";
    
    private static ClientDBHelper INSTANCE = null;
    
    private ClientDBHelper(Context context, String databaseName) {
        super(context, databaseName, null, DATABASE_VERSION);
    }
    
    public static void initDbHelper(Context context, User user) {
        INSTANCE = new ClientDBHelper(context, databaseNameForUser(user));
        LogUtil.info(TAG, "init db helper...");
    }
    
    private static String databaseNameForUser(User user) {
        return DATABASE_NAME + "." + user.id + ".db";
    }
    
    public static ClientDBHelper getInstance() {
        return INSTANCE;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        LogUtil.info(TAG, "executing create table sqls...");
        db.execSQL(SQL_CREATE_CATEGORY);
        db.execSQL(SQL_CREATE_ITEM);
        db.execSQL(SQL_CREATE_ITEM_PARAM);
        db.execSQL(SQL_CREATE_RECORD);
        db.execSQL(SQL_CREATE_RECORD_PARAM);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        LogUtil.info(TAG, "executing delete table sqls...");
        db.execSQL(SQL_DELETE_CATEGORY);
        db.execSQL(SQL_DELETE_ITEM);
        db.execSQL(SQL_DELET_ITEM_PARAM);
        db.execSQL(SQL_DELET_RECORD);
        db.execSQL(SQL_DELET_RECORD_PARAM);
        onCreate(db);
    }
    
    /***********************************************/
    /************Database operations****************/
    /***********************************************/
    
    private static final String[] CATEGORY_PROJECTIONS = {
        ModelColumn.BASE_COLUMN_ID,
        ModelColumn.BASE_COLUMN_USERID,
        ModelColumn.BASE_COLUMN_LASTMODIFIED,
        ModelColumn.BASE_COLUMN_DELETED,
        ModelColumn.BASE_COLUMN_DIRTY,
        ModelColumn.CATEGORY_COLUMN_NAME,
        ModelColumn.CATEGORY_COLUMN_DISPLAY_ORDER,
    };
    
    private static final String[] ITEM_PROJECTIONS = {
        ModelColumn.BASE_COLUMN_ID,
        ModelColumn.BASE_COLUMN_USERID,
        ModelColumn.BASE_COLUMN_LASTMODIFIED,
        ModelColumn.BASE_COLUMN_DELETED,
        ModelColumn.BASE_COLUMN_DIRTY,
        ModelColumn.ITEM_COLUMN_NAME,
        ModelColumn.ITEM_COLUMN_CATEGORYID,
        ModelColumn.ITEM_COLUMN_DISPLAY_INDEX,
        ModelColumn.ITEM_COLUMN_RECORD_COUNT,
    };
    
    private static final String[] ITEM_PARAM_PROJECTIONS = {
        ModelColumn.BASE_COLUMN_ID,
        ModelColumn.BASE_COLUMN_USERID,
        ModelColumn.BASE_COLUMN_LASTMODIFIED,
        ModelColumn.BASE_COLUMN_DELETED,
        ModelColumn.BASE_COLUMN_DIRTY,
        ModelColumn.ITEM_PARAM_COLUMN_ITEMID,
        ModelColumn.ITEM_PARAM_COLUMN_NAME,
        ModelColumn.ITEM_PARAM_COLUMN_TYPE,
        ModelColumn.ITEM_PARAM_COLUMN_IS_MUST,
        ModelColumn.ITEM_PARAM_COLUMN_DISPLAY_ORDER,
    };
    
    private static final String[] RECORD_PROJECTIONS = {
        ModelColumn.BASE_COLUMN_ID,
        ModelColumn.BASE_COLUMN_USERID,
        ModelColumn.BASE_COLUMN_LASTMODIFIED,
        ModelColumn.BASE_COLUMN_DELETED,
        ModelColumn.BASE_COLUMN_DIRTY,
        ModelColumn.RECORD_ITEM_ID,
        ModelColumn.RECORD_RECORD_TIME,
    };
    
    private static final String[] RECORD_PARAM_PROJECTIONS = {
        ModelColumn.BASE_COLUMN_ID,
        ModelColumn.BASE_COLUMN_USERID,
        ModelColumn.BASE_COLUMN_LASTMODIFIED,
        ModelColumn.BASE_COLUMN_DELETED,
        ModelColumn.BASE_COLUMN_DIRTY,
        ModelColumn.RECORD_PARAM_RECORD_ID,
        ModelColumn.RECORD_PARAM_ITEM_PARAM_ID,
        ModelColumn.RECORD_PARAM_VALUE,
    };
    
    private static final String[] getProjectionByTableName(String tableName) {
        if (Category.class.getSimpleName().equals(tableName)) {
            return CATEGORY_PROJECTIONS;
        } else if (Item.class.getSimpleName().equals(tableName)) {
            return ITEM_PROJECTIONS;
        } else if (ItemParam.class.getSimpleName().equals(tableName)) {
            return ITEM_PARAM_PROJECTIONS;
        } else if (Record.class.getSimpleName().equals(tableName)) {
            return RECORD_PROJECTIONS;
        } else if (RecordParam.class.getSimpleName().equals(tableName)) {
            return RECORD_PARAM_PROJECTIONS;
        }
        return null;
    }
    
    private static long lastTempId;
    private synchronized static long generateTempId() {
        long tempId = System.currentTimeMillis();
        if (tempId <= lastTempId) {
            tempId = lastTempId + 1;
        }
        lastTempId = tempId;
        return tempId;
    }

    public String dbGetMaxOrder(String tableName, String orderColumn) {
        SQLiteDatabase db = null;
        Cursor c = null;
        String maxOrder = null;
        try {
            String sql = "SELECT MAX(" + orderColumn + ")" + " FROM " + tableName;
            db = getReadableDatabase();
            c = db.rawQuery(sql, null);
            if (c.moveToFirst()) {
                maxOrder = c.getString(0);
            }
        } catch (Exception e) {
            LogUtil.error(TAG, "", e);
        } finally {
            try {
                c.close();
            } catch (Exception e) {
                LogUtil.error(TAG, "", e);
            }
            try {
                db.close();
            } catch (Exception e) {
                LogUtil.error(TAG, "", e);
            }
        }
        LogUtil.info(TAG, "dbGetMaxOrder: tableName = " + tableName + ", maxOrder = " + maxOrder);
        return maxOrder;
    }
    
    public synchronized void dbInsertOrUpdateModel(ClientBaseModel model) {
        SQLiteDatabase db = null;
        try {
            db = getWritableDatabase();
            _dbInsertOrUpdateModel(db, model);
        } catch (Exception e) {
            LogUtil.error(TAG, "", e);
        }  finally {
            try {
                db.close();
            } catch (Exception e) {
                LogUtil.error(TAG, "", e);
            }
        }
    }
    
    private void _dbInsertOrUpdateModel(SQLiteDatabase db, ClientBaseModel model) {
        if (model.getId() > 0) {
            _dbUpdateModel(db, model);
        } else {
            _dbInsertModel(db, model);
        }
    }

    private void _dbUpdateModel(SQLiteDatabase db, ClientBaseModel model) {
        ContentValues values = model.toContentValues();
        LogUtil.info(TAG, "db update: " + model);
        db.update(model.getClass().getSimpleName(), values, ModelColumn.BASE_COLUMN_ID + "=?",
                new String[] { String.valueOf(model.getId()) });
    }

    private void _dbInsertModel(SQLiteDatabase db, ClientBaseModel model) {
        if (model.getId() == 0)
            model.setId(generateTempId());
        ContentValues values = model.toContentValues();
        db.insert(model.getClass().getSimpleName(), null, values);
        LogUtil.info(TAG, "db insert: " + model);
    }
    
//    private void _dbDeleteItemParams(SQLiteDatabase db, Item item) {
//        StringBuffer sb = new StringBuffer("DELETE FROM ");
//        sb.append(ItemParam.class.getSimpleName());
//        sb.append(" WHERE ").append(ModelColumn.BASE_COLUMN_ID).append(" IN (");
//        int size = item.params.size();
//        for (int i = 0; i < size; i++) {
//            sb.append(item.params.get(i).getId());
//            if (i != size -1)
//                sb.append(",");
//        }
//        sb.append(")");
//    }
    
    public synchronized ArrayList<Category> dbQueryAllCategory() {
        return _dbQueryCategory(ModelColumn.BASE_COLUMN_DELETED + "=0");
    }
    
    private synchronized ArrayList<Category> _dbQueryCategory(String where) {
        SQLiteDatabase db = null;
        Cursor c = null;
        String orderBy = ModelColumn.CATEGORY_COLUMN_DISPLAY_ORDER + " ASC";
        LogUtil.info(TAG, "dbQueryAllCategory");
        try {
            db = getReadableDatabase();
            c = db.query(Category.class.getSimpleName(), CATEGORY_PROJECTIONS, where, null, null, null, orderBy);
            ArrayList<Category> list = new ArrayList<Category>(c.getCount() + 1);
            while (c.moveToNext()) {
                Category category = new Category();
                category.fromCursor(c);
                list.add(category);
            }
            _dumpModelList(list);
            return list;
        } catch (Exception e) {
            LogUtil.error(TAG, "", e);
            return new ArrayList<Category>();
        } finally {
            try {
                c.close();
            } catch (Exception e) {
                LogUtil.error(TAG, "", e);
            }
            try {
                db.close();
            } catch (Exception e) {
                LogUtil.error(TAG, "", e);
            }
        }
    }
    
    public synchronized void dbInsertOrUpdateItem(Item item, ArrayList<ItemParam> deletedParams) {
        SQLiteDatabase db = null;
        LogUtil.info(TAG, "dbInsertOrUpdateItem");
        try {
            db = getWritableDatabase();
            db.beginTransaction();
            _dbInsertOrUpdateModel(db, item);
            /*
             * TODO what about those deleted params by UI action, handle on UI
             * or here?
             */
            int paramSize = item.params.size();
            for (int i = 0; i < paramSize; i++) {
                ItemParam param = item.params.get(i);
                if (!param.dirty)
                    continue;
                param.itemId = item.getId();
                _dbInsertOrUpdateModel(db, param);
            }
            int deletes = deletedParams.size();
            for (int i = 0; i < deletes; i++) {
                _dbDeleteModel(db, deletedParams.get(i));
            }
            db.setTransactionSuccessful();
        } catch (Exception e) {
            LogUtil.error(TAG, "", e);
        } finally {
            try {
                db.endTransaction();
                db.close();
            } catch (Exception e) {
                LogUtil.error(TAG, "", e);
            }
        }

    }
    
    public synchronized ArrayList<Item> dbQueryAllItem() {
        SQLiteDatabase db = null;
        Cursor c = null;
        String itemTable = Item.class.getSimpleName();
        String itemParamTable = ItemParam.class.getSimpleName();
        StringBuffer sb = new StringBuffer("SELECT ");
        sb.append(itemTable).append(".").append(ModelColumn.BASE_COLUMN_ID).append(" AS itemid");
        sb.append(",");
        sb.append(itemTable).append(".").append(ModelColumn.BASE_COLUMN_USERID).append(" AS itemuserid");
        sb.append(",");
        sb.append(itemTable).append(".").append(ModelColumn.BASE_COLUMN_LASTMODIFIED).append(" AS itemlastmodified");
        sb.append(",");
        sb.append(itemTable).append(".").append(ModelColumn.BASE_COLUMN_DELETED).append(" AS itemdeleted");
        sb.append(",");
        sb.append(itemTable).append(".").append(ModelColumn.BASE_COLUMN_DIRTY).append(" AS itemdirty");
        sb.append(",");
        
        sb.append(itemParamTable).append(".").append(ModelColumn.BASE_COLUMN_ID).append(" AS itemparamid");
        sb.append(",");
        sb.append(itemParamTable).append(".").append(ModelColumn.BASE_COLUMN_USERID).append(" AS itemparamuserid");
        sb.append(",");
        sb.append(itemParamTable).append(".").append(ModelColumn.BASE_COLUMN_LASTMODIFIED).append(" AS itemparamlastmodified");
        sb.append(",");
        sb.append(itemParamTable).append(".").append(ModelColumn.BASE_COLUMN_DELETED).append(" AS itemparamdeleted");
        sb.append(",");
        sb.append(itemParamTable).append(".").append(ModelColumn.BASE_COLUMN_DIRTY).append(" AS itemparamdirty");
        sb.append(",");
        
        sb.append(itemTable).append(".").append(ModelColumn.ITEM_COLUMN_NAME).append(" AS itemname");
        sb.append(",");
        sb.append(itemTable).append(".").append(ModelColumn.ITEM_COLUMN_CATEGORYID).append(" AS itemcategoryid");
        sb.append(",");
        sb.append(itemTable).append(".").append(ModelColumn.ITEM_COLUMN_DISPLAY_INDEX).append(" AS itemdisplayindex");
        sb.append(",");
        sb.append(itemTable).append(".").append(ModelColumn.ITEM_COLUMN_RECORD_COUNT).append(" AS itemrecordcount");
        sb.append(",");
        
        sb.append(itemParamTable).append(".").append(ModelColumn.ITEM_PARAM_COLUMN_NAME).append(" AS itemparamname");
        sb.append(",");
        sb.append(itemParamTable).append(".").append(ModelColumn.ITEM_PARAM_COLUMN_TYPE).append(" AS itemparamtype");
        sb.append(",");
        sb.append(itemParamTable).append(".").append(ModelColumn.ITEM_PARAM_COLUMN_IS_MUST).append(" AS itemparamismust");
        sb.append(",");
        sb.append(itemParamTable).append(".").append(ModelColumn.ITEM_PARAM_COLUMN_DISPLAY_ORDER).append(" AS itemparamdisplayorder");

        sb.append(" FROM ");
        sb.append(itemTable).append(" LEFT JOIN ").append(itemParamTable).append(" ON ");
        sb.append(itemTable).append(".").append(ModelColumn.BASE_COLUMN_ID);
        sb.append("=").append(itemParamTable).append(".").append(ModelColumn.ITEM_PARAM_COLUMN_ITEMID);
        sb.append(" WHERE ");
        sb.append(itemTable).append(".").append(ModelColumn.BASE_COLUMN_DELETED).append("=0");
        sb.append(" ORDER BY ");
        sb.append(itemTable).append(".").append(ModelColumn.ITEM_COLUMN_DISPLAY_INDEX).append(" ASC");
        sb.append(",");
        sb.append(itemParamTable).append(".").append(ModelColumn.ITEM_PARAM_COLUMN_DISPLAY_ORDER).append(" ASC");
        String sql = sb.toString();
        LogUtil.info(TAG, "dbQueryAllItem");
        try {
            db = getReadableDatabase();
            c = db.rawQuery(sql, null);
            ArrayList<Item> list = new ArrayList<Item>(c.getCount() + 1);
            Item lastItem = null;
            Item currentItem;
            while (c.moveToNext()) {
                long itemid = c.getLong(c.getColumnIndex("itemid"));
                if (lastItem != null && lastItem.getId() == itemid) {
                    currentItem = lastItem;
                } else {
                    currentItem = new Item();
                    currentItem.setId(itemid);
                    currentItem.userId = c.getLong(c.getColumnIndex("itemuserid"));
                    currentItem.lastmodified = c.getLong(c.getColumnIndex("itemlastmodified"));
                    currentItem.deleted = c.getInt(c.getColumnIndex("itemdeleted")) > 0;
                    currentItem.dirty = c.getInt(c.getColumnIndex("itemdirty")) > 0;
                    currentItem.itemName = c.getString(c.getColumnIndex("itemname"));
                    currentItem.categoryId = c.getLong(c.getColumnIndex("itemcategoryid"));
                    currentItem.displayIndex = c.getString(c.getColumnIndex("itemdisplayindex"));
                    currentItem.recordCount = c.getInt(c.getColumnIndex("itemrecordcount"));
                    lastItem = currentItem;
                    list.add(currentItem);
                }
                long itemparamid = c.getLong(c.getColumnIndex("itemparamid"));
                if (itemparamid == 0)
                    continue;
                boolean itemparamdeleted = c.getInt(c.getColumnIndex("itemparamdeleted")) > 0;
                if (itemparamdeleted)
                    continue;
                ItemParam itemParam = new ItemParam();
                itemParam.setId(itemparamid);
                itemParam.userId = c.getLong(c.getColumnIndex("itemparamuserid"));
                itemParam.lastmodified = c.getLong(c.getColumnIndex("itemparamlastmodified"));
                itemParam.deleted = itemparamdeleted;
                itemParam.dirty = c.getInt(c.getColumnIndex("itemparamdirty")) > 0;
                itemParam.itemId = itemid;
                itemParam.itemParamName = c.getString(c.getColumnIndex("itemparamname"));
                itemParam.paramType = c.getInt(c.getColumnIndex("itemparamtype"));
                itemParam.isMust = c.getInt(c.getColumnIndex("itemparamismust")) > 0;
                itemParam.displayOrder = c.getString(c.getColumnIndex("itemparamdisplayorder"));

                currentItem.params.add(itemParam);
            }
            _dumpItemList(list);
            return list;
        } catch (Exception e) {
            LogUtil.error(TAG, "", e);
            return new ArrayList<Item>();
        } finally {
            try {
                c.close();
            } catch (Exception e) {
                LogUtil.error(TAG, "", e);
            }
            try {
                db.close();
            } catch (Exception e) {
                LogUtil.error(TAG, "", e);
            }
        }
        
//        try {
//            String orderBy = ModelColumn.ITEM_COLUMN_DISPLAY_INDEX + " ASC";
//            db = getReadableDatabase();
//            Cursor c = db.query(Item.class.getSimpleName(), ITEM_PROJECTIONS, ModelColumn.BASE_COLUMN_DELETED + "=0",
//                    null, null, null, orderBy);
//            ArrayList<Item> list = new ArrayList<Item>(c.getCount() + 1);
//            while (c.moveToNext()) {
//                Item item = new Item();
//                item.fromCursor(c);
//                list.add(item);
//            }
//            c.close();
//            /* begin query item param, may need to refine */
//            String selection = ModelColumn.BASE_COLUMN_DELETED + "=0 AND " + ModelColumn.ITEM_PARAM_COLUMN_ITEMID
//                    + "=?";
//            int size = list.size();
//            for (int i = 0; i < size; i++) {
//                Item item = list.get(i);
//                String[] selectionArgs = { String.valueOf(item.getId()) };
//                c = db.query(ItemParam.class.getSimpleName(), ITEM_PARAM_PROJECTIONS, selection, selectionArgs, null,
//                        null, null);
//                while (c.moveToNext()) {
//                    ItemParam ip = new ItemParam();
//                    ip.fromCursor(c);
//                    ip.itemId = item.getId();
//                    item.params.add(ip);
//                }
//                c.close();
//            }
//            _dumpItemList(list);
//            return list;
//        } catch (Exception e) {
//            LogUtil.error(TAG, "", e);
//            return new ArrayList<Item>();
//        } finally {
//            try {
//                db.close();
//            } catch (Exception e) {
//                LogUtil.error(TAG, "", e);
//            }
//        }
    }
    
    @SuppressWarnings("rawtypes")
    private void _dumpModelList(ArrayList models) {
        if (LogUtil.ENABLED) {
            LogUtil.info(TAG, "######BEGIN DUMP CATEGORY LIST######");
            int size = models.size();
            for (int i = 0; i < size; i++) {
                ClientBaseModel model = (ClientBaseModel)models.get(i);
                LogUtil.info(TAG, model);
            }
            LogUtil.info(TAG, "######END   DUMP CATEGORY LIST######");
        }
    }
    
    private void _dumpItemList(ArrayList<Item> items) {
        if (LogUtil.ENABLED) {
            LogUtil.info(TAG, "######BEGIN DUMP ITEM LIST######");
            int size = items.size();
            for (int i = 0; i < size; i++) {
                Item item = items.get(i);
                LogUtil.info(TAG, item);
                for (int j = 0; j < item.params.size(); j++) {
                    LogUtil.info(TAG, item.params.get(j));
                }
            }
            LogUtil.info(TAG, "######END   DUMP ITEM LIST######");
        }
    }
    
    public synchronized void dbSaveRecord(Record record, ArrayList<RecordParam> paramList) {
        SQLiteDatabase db = null;
        LogUtil.info(TAG, "dbRecord");
        try {
            db = getWritableDatabase();
            db.beginTransaction();
            if (record.getId() == 0)
                _dbInsertOrUpdateModel(db, record);
            int paramSize = paramList.size();
            for (int i = 0; i < paramSize; i++) {
                RecordParam param = paramList.get(i);
                param.recordId = record.getId();
                _dbInsertOrUpdateModel(db, param);
            }
            db.setTransactionSuccessful();
        } catch (Exception e) {
            LogUtil.error(TAG, "", e);
        } finally {
            try {
                db.endTransaction();
                db.close();
            } catch (Exception e) {
                LogUtil.error(TAG, "", e);
            }
        }

    }
    
    public synchronized int dbQueryRecordCount(long itemId) {
        SQLiteDatabase db = null;
        Cursor c = null;
        String recordTable = Record.class.getSimpleName();
        StringBuffer sb = new StringBuffer("SELECT COUNT(*) AS count FROM ");
        sb.append(recordTable);
        sb.append(" WHERE ");
        sb.append(ModelColumn.RECORD_ITEM_ID).append("=").append(itemId);
        sb.append(" AND ");
        sb.append(ModelColumn.BASE_COLUMN_DELETED).append("=0");
        String sql = sb.toString();
        try {
            db = getReadableDatabase();
            c = db.rawQuery(sql, null);
            if (c.moveToFirst()) {
                int count = c.getInt(c.getColumnIndex("count"));
                LogUtil.info(TAG, "dbQueryRecordCount: " + count);
                return count;
            }
            return 0;
        } catch (Exception e) {
            LogUtil.error(TAG, "", e);
            return 0;
        } finally {
            try {
                c.close();
            } catch (Exception e) {
                LogUtil.error(TAG, "", e);
            }
            try {
                db.close();
            } catch (Exception e) {
                LogUtil.error(TAG, "", e);
            }
        }
    }
    
    public synchronized ArrayList<Record> dbQueryRecord(long itemId) {
        SQLiteDatabase db = null;
        Cursor c = null;
        String recordTable = Record.class.getSimpleName();
        String recordParamTable = RecordParam.class.getSimpleName();
        StringBuffer sb = new StringBuffer("SELECT ");
        sb.append(recordTable).append(".").append(ModelColumn.BASE_COLUMN_ID).append(" AS recordid");
        sb.append(",");
        sb.append(recordTable).append(".").append(ModelColumn.BASE_COLUMN_USERID).append(" AS recorduserid");
        sb.append(",");
        sb.append(recordTable).append(".").append(ModelColumn.BASE_COLUMN_LASTMODIFIED).append(" AS recordlastmodified");
        sb.append(",");
        sb.append(recordTable).append(".").append(ModelColumn.BASE_COLUMN_DELETED).append(" AS recorddeleted");
        sb.append(",");
        sb.append(recordTable).append(".").append(ModelColumn.BASE_COLUMN_DIRTY).append(" AS recorddirty");
        sb.append(",");
        
        sb.append(recordParamTable).append(".").append(ModelColumn.BASE_COLUMN_ID).append(" AS recordparamid");
        sb.append(",");
        sb.append(recordParamTable).append(".").append(ModelColumn.BASE_COLUMN_USERID).append(" AS recordparamuserid");
        sb.append(",");
        sb.append(recordParamTable).append(".").append(ModelColumn.BASE_COLUMN_LASTMODIFIED).append(" AS recordparamlastmodified");
        sb.append(",");
        sb.append(recordParamTable).append(".").append(ModelColumn.BASE_COLUMN_DELETED).append(" AS recordparamdeleted");
        sb.append(",");
        sb.append(recordParamTable).append(".").append(ModelColumn.BASE_COLUMN_DIRTY).append(" AS recordparamdirty");
        sb.append(",");
        
        sb.append(recordTable).append(".").append(ModelColumn.RECORD_ITEM_ID).append(" AS recorditemid");
        sb.append(",");
        sb.append(recordTable).append(".").append(ModelColumn.RECORD_RECORD_TIME).append(" AS recordrecordtime");
        sb.append(",");
        
        sb.append(recordParamTable).append(".").append(ModelColumn.RECORD_PARAM_ITEM_PARAM_ID).append(" AS recordparamitemparamid");
        sb.append(",");
        sb.append(recordParamTable).append(".").append(ModelColumn.RECORD_PARAM_VALUE).append(" AS recordparamvalue");
        sb.append(" FROM ");
        sb.append(recordTable).append(" LEFT JOIN ").append(recordParamTable).append(" ON ");
        sb.append(recordTable).append(".").append(ModelColumn.BASE_COLUMN_ID);
        sb.append("=").append(recordParamTable).append(".").append(ModelColumn.RECORD_PARAM_RECORD_ID);
        sb.append(" WHERE ");
        sb.append(recordTable).append(".").append(ModelColumn.RECORD_ITEM_ID).append("=").append(itemId);
        sb.append(" AND ");
        sb.append(recordTable).append(".").append(ModelColumn.BASE_COLUMN_DELETED).append("=0");
        String sql = sb.toString();
        LogUtil.info(TAG, "dbQueryRecord");
        try {
            db = getReadableDatabase();
            c = db.rawQuery(sql, null);
            ArrayList<Record> list = new ArrayList<Record>(c.getCount() + 1);
            Record lastRecord = null;
            Record currentRecord;
            while (c.moveToNext()) {
                long recordid = c.getLong(c.getColumnIndex("recordid"));
                if (lastRecord != null && lastRecord.getId() == recordid) {
                    currentRecord = lastRecord;
                } else {
                    currentRecord = new Record();
                    currentRecord.setId(recordid);
                    currentRecord.userId = c.getLong(c.getColumnIndex("recorduserid"));
                    currentRecord.lastmodified = c.getLong(c.getColumnIndex("recordlastmodified"));
                    currentRecord.deleted = c.getInt(c.getColumnIndex("recorddeleted")) > 0;
                    currentRecord.dirty = c.getInt(c.getColumnIndex("recorddirty")) > 0;
                    
                    currentRecord.itemId = c.getLong(c.getColumnIndex("recorditemid"));
                    currentRecord.recordTime = c.getLong(c.getColumnIndex("recordrecordtime"));
                    lastRecord = currentRecord;
                    list.add(currentRecord);
                }
                long recordparamid = c.getLong(c.getColumnIndex("recordparamid"));
                if (recordparamid == 0)
                    continue;
                boolean recordparamdeleted = c.getInt(c.getColumnIndex("recordparamdeleted")) > 0;
                if (recordparamdeleted)
                    continue;
                RecordParam recordParam = new RecordParam();
                recordParam.setId(recordparamid);
                recordParam.userId = c.getLong(c.getColumnIndex("recordparamuserid"));
                recordParam.lastmodified = c.getLong(c.getColumnIndex("recordparamlastmodified"));
                recordParam.deleted = recordparamdeleted;
                recordParam.dirty = c.getInt(c.getColumnIndex("recordparamdirty")) > 0;
                
                recordParam.recordId = recordid;
                recordParam.itemParamId = c.getLong(c.getColumnIndex("recordparamitemparamid"));
                recordParam.value = c.getString(c.getColumnIndex("recordparamvalue"));
                currentRecord.paramList.add(recordParam);
            }
            return list;
        } catch (Exception e) {
            LogUtil.error(TAG, "", e);
            return new ArrayList<Record>();
        } finally {
            try {
                c.close();
            } catch (Exception e) {
                LogUtil.error(TAG, "", e);
            }
            try {
                db.close();
            } catch (Exception e) {
                LogUtil.error(TAG, "", e);
            }
        }
    }
    
    /**
     * when delete item or record, only delete the item or record, don't touch
     * the itemparams or recordparams, because they are un-reachable
     * 
     * @param model
     */
    public synchronized void dbDeleteModel(ClientBaseModel model) {
        SQLiteDatabase db = null;
        try {
            db = getWritableDatabase();
            _dbDeleteModel(db, model);
        } catch (Exception e) {
            LogUtil.error(TAG, "", e);
        } finally {
            try {
                db.close();
            } catch (Exception e) {
                LogUtil.error(TAG, "", e);
            }
        }
    }
    
    private void _dbDeleteModel(SQLiteDatabase db, ClientBaseModel model) {
        ContentValues values = new ContentValues();
        values.put(ModelColumn.BASE_COLUMN_DELETED, true);
        values.put(ModelColumn.BASE_COLUMN_DIRTY, true);
        LogUtil.info(TAG, "db delete: " + model);
        db.update(model.getClass().getSimpleName(), values, ModelColumn.BASE_COLUMN_ID + "=?",
                new String[] { String.valueOf(model.getId()) });
    }
    
    ///////////////////////////////////////////////////////
    ///////////////////////SYNC////////////////////////////
    ///////////////////////////////////////////////////////
    
    public long dbGetMaxLastmodifiedTime(String tableName) {
        SQLiteDatabase db = null;
        Cursor c = null;
        long time = 0L;
        try {
            String sql = "SELECT MAX(" + ModelColumn.BASE_COLUMN_LASTMODIFIED + ")" + " FROM " + tableName;
            db = getReadableDatabase();
            c = db.rawQuery(sql, null);
            if (c.moveToFirst()) {
                time = c.getLong(0);
            }
        } catch (Exception e) {
            LogUtil.error(TAG, "", e);
        } finally {
            try {
                c.close();
            } catch (Exception e) {
                LogUtil.error(TAG, "", e);
            }
            try {
                db.close();
            } catch (Exception e) {
                LogUtil.error(TAG, "", e);
            }
        }
        LogUtil.info(TAG, "dbGetMaxLastmodifiedTime: tableName = " + tableName + ", maxLastmodified = " + time);
        return time;
    }
    
    @SuppressWarnings("rawtypes")
    public void dbServer2ClientSyncModels(ArrayList<ArrayList> updateCollection) {
        SQLiteDatabase db = null;
        LogUtil.info(TAG, "dbServer2ClientSyncModels");
        try {
            db = getWritableDatabase();
            db.beginTransaction();
            final int tableSize = updateCollection.size();
            for (int tableIndex = 0; tableIndex < tableSize; tableIndex++) {
                ArrayList modelList = updateCollection.get(tableIndex);
                final int modelSize = modelList.size();
                for (int modelIndex = 0; modelIndex < modelSize; modelIndex++) {
                    ClientBaseModel model = (ClientBaseModel)modelList.get(modelIndex);
                    _dbInsertOrUpdateModel(db, model);
                }
            }
            db.setTransactionSuccessful();
        } catch (Exception e) {
            LogUtil.error(TAG, "", e);
        } finally {
            try {
                db.endTransaction();
                db.close();
            } catch (Exception e) {
                LogUtil.error(TAG, "", e);
            }
        }
    }
    
    @SuppressWarnings({ "rawtypes", "unchecked" })
    public ArrayList dbQueryDirtyModels(String tableName) {
        SQLiteDatabase db = null;
        Cursor c = null;
        String[] projection = getProjectionByTableName(tableName);
        LogUtil.info(TAG, "dbQueryDirtyModels: tableName = " + tableName);
        try {
            db = getReadableDatabase();
            c = db.query(tableName, projection, ModelColumn.BASE_COLUMN_DIRTY + "=1", null, null, null, null);
            ArrayList list = new ArrayList(c.getCount() + 1);
            while (c.moveToNext()) {
                ClientBaseModel model = ModelUtil.createModel(tableName);
                if (model != null) {
                    model.fromCursor(c);
                    list.add(model);
                }
            }
            _dumpModelList(list);
            return list;
        } catch (Exception e) {
            LogUtil.error(TAG, "", e);
            return new ArrayList();
        } finally {
            try {
                c.close();
            } catch (Exception e) {
                LogUtil.error(TAG, "", e);
            }
            try {
                db.close();
            } catch (Exception e) {
                LogUtil.error(TAG, "", e);
            }
        }
    }
    
    public void dbHandleClient2ServerSyncFeedback(ArrayList<Client2ServerSyncFeedback> feedbackList) {
        SQLiteDatabase db = null;
        LogUtil.info(TAG, "dbHandleClient2ServerSyncFeedback");
        try {
            db = getWritableDatabase();
            db.beginTransaction();
            final int feedbackSize = feedbackList.size();
            for (int feedbackIndex = 0; feedbackIndex < feedbackSize; feedbackIndex++) {
                Client2ServerSyncFeedback feedback = feedbackList.get(feedbackIndex);
                final int modelSize = feedback.simpleBaseModelList.size();
                for (int modelIndex = 0; modelIndex < modelSize; modelIndex++) {
                    SimpleBaseModel simpleModel = feedback.simpleBaseModelList.get(modelIndex);
                    StringBuilder updateSql = new StringBuilder("UPDATE ");
                    updateSql.append(feedback.tableName).append(" SET ");
                    updateSql.append(ModelColumn.BASE_COLUMN_LASTMODIFIED).append("=").append(simpleModel.lastmodified);
                    updateSql.append(", ");
                    updateSql.append(ModelColumn.BASE_COLUMN_DIRTY).append("=0");//clear dirty flag
                    updateSql.append(" WHERE ").append(ModelColumn.BASE_COLUMN_ID).append("=").append(simpleModel.id);
                    db.execSQL(updateSql.toString());
                }
            }
            db.setTransactionSuccessful();
        } catch (Exception e) {
            LogUtil.error(TAG, "", e);
        } finally {
            try {
                db.endTransaction();
                db.close();
            } catch (Exception e) {
                LogUtil.error(TAG, "", e);
            }
        }
    }

}
